﻿using System;
using System.Collections.Generic;
using System.Text;

/*
    Class to help with sucking in all of the Service broker stuff.
    This wraps the SQL used

*/
/*
R&D can we add this to show queue size?
Service broker queues are stored as internal tables. The queue is listed as the parent object of the hidden table. 
By linking the internal table to sys.partitions, you can use the sys.partitions.rows column as a quick count bearing 
in mind that it may not always be 100%. The accuracy is probably not an issue for you if you are using the nolock 
already as that may count records that have not been committed yet.
 
Select rows
From sys.partitions P
Inner Join sys.internal_tables IT On IT.object_id = P.object_id
Where IT.parent_object_id = object_id('<SB Queue Name>')
And P.index_id In (1, 0) 
Edit: Another Version of Query
 
In follow-up conversation, I wrote a version of the query that can be used to query the count of service broker queues in a different database. Since the OBJECT_ID funtion does not accept a database ID parameter like OBJECT_NAME does, we instead need to join to the sys.service_queues view to link the queue to the internal table. For the below example query, I am querying for a service broker queue in msdb.
 Select P.rows
From msdb.sys.partitions P
Inner Join msdb.sys.internal_tables IT On IT.object_id = P.object_id
Inner Join msdb.sys.service_queues SQ On SQ.object_id = IT.parent_object_id
Where SQ.name = '<Queue name>'
And P.index_id In (1, 0) 
(No Ratings Yet)
select p.ROWs,* from sys.service_queues q
Inner Join sys.internal_tables IT On IT.parent_object_id = q.object_id
join  sys.partitions P on  IT.object_id = P.object_id 
where P.index_id In (1, 0) 

*/
namespace SqlServerDriver
{
    class BrokerTemplates
    {
		public static String GetBrokerTemplate(DbView.Command command)
		{
			return (command.Name == Cmd.ServiceBroker) ? @"
select s.name as Service, m.name as Message, q.name as Queue, q.activation_procedure as ProcedureName,  c.name as Contract
 from sys.service_contracts c
 left join sys.service_contract_usages u on u.service_contract_id = c.service_contract_id
 left join sys.service_contract_message_usages mu on mu.service_contract_id = c.service_contract_id
 left join sys.service_message_types m on m.message_type_id = mu.message_type_id
 left join sys.services s on s.service_id = u.service_id
 left join sys.service_queue_usages qu on qu.service_id = s.service_id
 left join sys.service_queues q on q.object_id = qu.service_queue_id
where q.activation_procedure is not NULL
   [[ and (UPPER(q.activation_procedure) like '%%' 
   or UPPER(m.name) like '%%' 
   or UPPER(c.name) like '%%' 
   or UPPER(q.name) like '%%' 
   or UPPER(s.name) like '%%' ) ]]
 order by s.name,q.name,m.name"

					: (command.Name == Cmd.BrokerMessages) ? @"
   select m.name as Message, c.name as Contract, s.name as Service, 
     m.message_type_id as type_id, m.principal_id, m.validation, m.validation_desc, m.xml_collection_id
     from sys.service_message_types m 
left join sys.service_contract_message_usages mu on m.message_type_id = mu.message_type_id
left join sys.service_contracts c  on mu.service_contract_id = c.service_contract_id
left join sys.service_contract_usages u on u.service_contract_id = c.service_contract_id
left join sys.services s on u.service_id = s.service_id
left join sys.service_queue_usages qu on qu.service_id = s.service_id
left join sys.service_queues q on q.object_id = qu.service_queue_id
where UPPER(ISNULL(m.name,'')) not like '%MICROSOFT%' 
and
(  UPPER(m.name) like '%%' 
   or UPPER(c.name) like '%%' 
   or UPPER(s.name) like '%%' )
order by m.name, c.name, s.name   
" :
					(command.Name == Cmd.BrokerContracts) ? @"
   select c.name as Contract, m.name as Message, 
       case when mu.is_sent_by_initiator = 1 and mu.is_sent_by_target = 1 then 'ANY'
            when mu.is_sent_by_initiator = 1 then 'INITIATOR'      
            when mu.is_sent_by_target = 1 then 'TARGET'
            else '' end as SENT_BY
from  sys.service_contracts c 
left join sys.service_contract_message_usages mu on mu.service_contract_id = c.service_contract_id
left join sys.service_message_types m on m.message_type_id = mu.message_type_id
where UPPER(c.name) not like '%MICROSOFT%'   
   and (UPPER(m.name) like '%%' 
      or UPPER(c.name) like '%%')
order by c.name, m.name
" :
//                    (command.Name == Cmd.BrokerQueues) ? @"
//select  q.name as Queue, null as Msgs,s.name as Service, q.activation_procedure as ProcedureName,
//    case when is_activation_enabled = 1 then 'Yes' else '* No ' end as activation,
//    max_readers,
//    case when is_receive_enabled = 1 then 'On' else '* Off' end as [STATUS],
//    case when is_retention_enabled = 1 then '* On' else 'Off' end as retention,
//    modify_date, object_id, q.principal_id, schema_id, parent_object_id,type,type_desc,
//    create_date, 
//    is_ms_shipped, 
//    is_published, 
//    is_schema_published, 
//    execute_as_principal_id,
//    case when is_enqueue_enabled = 1 then 'Yes' else '* No ' end as queueing
//  from sys.service_queues q 
//left join sys.service_queue_usages qu on q.object_id = qu.service_queue_id
//left join sys.services s on qu.service_id = s.service_id
//where UPPER(ISNULL(s.name,'')) not like '%MICROSOFT%' 
//  and q.name like '%%'
//" :
					(command.Name == Cmd.BrokerQueues) ? @"
select  q.name as Queue, p.rows as Msgs, q.activation_procedure as ProcedureName,
    case when is_activation_enabled = 1 then 'Yes' else '* No ' end as activation,
    max_readers,
    case when is_receive_enabled = 1 then 'On' else '* Off' end as [STATUS],
    case when is_retention_enabled = 1 then '* On' else 'Off' end as retention,
    q.modify_date, q.object_id, q.principal_id, q.schema_id, q.parent_object_id,q.type,q.type_desc,
    q.create_date, q.is_ms_shipped, q.is_published, q.is_schema_published, 
    execute_as_principal_id,
    case when is_enqueue_enabled = 1 then 'Yes' else '* No ' end as queueing
  from sys.service_queues q 
  Join sys.internal_tables it on it.parent_object_id = q.object_id
  join  sys.partitions p on  it.object_id = p.object_id 
where (p.index_id In (1, 0) /* and UPPER(ISNULL(s.name,'')) not like '%MICROSOFT%' */)
  and q.name like '%%'
" :
//left join sys.service_queue_usages qu on q.object_id = qu.service_queue_id
////left join sys.services s on qu.service_id = s.service_id

					(command.Name == Cmd.BrokerServices) ? @"
select s.name as service, q.name as queue_name, q.activation_procedure, 
       s.service_id, s.principal_id, s.service_queue_id,
       case when perm.major_id is null then 'No' else '* Yes' end as Permission
 from sys.services s
 left join sys.service_queue_usages qu on qu.service_id = s.service_id
 left join sys.service_queues q on q.object_id = qu.service_queue_id
 left join sys.database_permissions perm on s.service_id = perm.major_id and perm.permission_name='SEND' where (s.name like '%%' 
       or q.name like '%%' 
       or q.activation_procedure like '%%')
   and s.name not like '%microsoft.com%' 
 order by s.name
" :
					(command.Name == Cmd.BrokerRoutes) ? @"
select name as route_name, address, remote_service_name, broker_instance, 
               route_id, principal_id, lifetime, mirror_address
from sys.routes where name like '%%' or remote_service_name like '%%'
  " :
					(command.Name == Cmd.BrokerEndpoints) ? @"
select 
ce.state_desc,
s.name as service_name,
ce.far_service,
c.name as contract_name,
ce.far_broker_instance,
case when db.name is null then 
     case when ce.far_broker_instance is null then '' 
                              else '* remote *' end
     when db.name = DB_NAME() then '* this *' 
else db.name end as 'far_database',
ce.conversation_handle,
ce.conversation_id
from sys.conversation_endpoints ce
left join sys.services s on s.service_id = ce.service_id
left join sys.service_contracts c on c.service_contract_id = ce.service_contract_id 
left join sys.databases db on db.service_broker_guid = far_broker_instance
                           and db.is_broker_enabled = 1

where far_service like '%%'
   or s.name like '%%'
   or c.name like '%%'
   or far_broker_instance like '%%'
order by s.name,ce.far_service" :
					(command.Name == Cmd.BrokerXmitQueue) ? @"
select top 1000
message_type_name, from_service_name, to_service_name, 
transmission_status, conversation_handle, to_broker_instance, 
enqueue_time, message_sequence_number, is_conversation_error, is_end_of_dialog,
cast(message_body as nvarchar(max)) as message_body
  from sys.transmission_queue 
 where to_service_name like '%%'
    or from_service_name like '%%'
    or service_contract_name like '%%'" :
					(command.Name == Cmd.BrokerDatabases) ? @"
select database_id as id, name, service_broker_guid as broker_instance,
  case when is_broker_enabled = 1 then 'Yes' else '* No' end as Broker_Enabled,
  case when is_trustworthy_on = 1 then '* Yes' else 'No' end as Set_Trustworthy,
  case when is_master_key_encrypted_by_server = 1 then '* Yes' else 'No' end as Key_Encrypted
  from sys.databases
 where (name like '%%' or service_broker_guid like '%%')
 order by name " :
					(command.Name == Cmd.BrokerQueueItems) ? @"
select 
status, priority, queuing_order,
conversation_handle, 
message_sequence_number as seq, 
message_type_name,
cast(message_body as NVARCHAR(max)) as message_data
from {0} -- where {1}
" :
					(command.Name == Cmd.BrokerQueueItemsNoData) ? @"
select 
status, priority, queuing_order,
conversation_handle, 
message_sequence_number as seq, 
message_type_name
from {0} -- where {1}
" :

			@"";
		}
    }
}
